package com.qf.Dao.impl;

import com.qf.Dao.UserDao;
import com.qf.Utils.DBUtils;
import com.qf.Utils.VerifyCodeUtils;
import com.qf.entity.SysUser;
import com.qf.entity.UserManagEmentPaginationDisplay;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class UserDaoImpl implements UserDao {
    private QueryRunner qr = new QueryRunner(DBUtils.DB.getDs());

    /**
     * 查询总页数
     *
     * @return
     */
    @Override
    public Integer selectCount() throws SQLException {
        String sql = "select count(1) from sys_user";
        Long l = (Long) qr.query(sql, new ScalarHandler());
        return l == null ? 0 : Integer.parseInt(l + "");
    }

    /**
     * 分页
     *
     * @param currentPageNo
     * @param pageSize
     * @return
     */
    @Override
    public List<UserManagEmentPaginationDisplay> selectByUser(Integer currentPageNo, Integer pageSize) throws SQLException {
        String sql = "SELECT u.id,u.`name`,u.sex,r.`name` as `部门`, d.simplename,u.email,u.phone,u.createtime,u.`status`\n" +
                "FROM sys_user as u \n" +
                "LEFT JOIN sys_role as r ON u.deptid = r.deptid \n" +
                "LEFT JOIN sys_dept as d ON r.num=d.num   limit ?,?";
        Object[] obj = {(currentPageNo - 1) * pageSize, pageSize};
        List<UserManagEmentPaginationDisplay> userList = qr.query(sql, new BeanListHandler<UserManagEmentPaginationDisplay>(UserManagEmentPaginationDisplay.class), obj);
        return userList;
    }

    /**
     * 根据id查询
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public List<SysUser> idByUser(int id) throws SQLException {
        String sql = "SELECT account,password,name,sex,deptid,email,phone,`status`,createtime,id FROM sys_user WHERE id=?";
        List<SysUser> userList = qr.query(sql, new BeanListHandler<SysUser>(SysUser.class), id);
        return userList;
    }

    /**
     * 添加
     *
     * @param u
     * @return
     * @throws SQLException
     */
    @Override
    public int addUser(SysUser u) throws SQLException {

        String sql = "insert into sys_user(account,password,name,sex,deptid,email,phone,`status`,createtime)\n" +
                "values(?,?,?,?,?,?,?,?,?)";
        //获取时间
        Date createtime = new Date();
        Object[] objects = {u.getAccount(), u.getPassword(), u.getName(), u.getSex(), u.getDeptid(), u.getEmail(), u.getPhone(), u.getStatus(), createtime};
        int num = qr.update(sql, objects);
        return num;
    }

    /**
     * 修改
     *
     * @param u
     * @return
     */
    @Override
    public int modify(SysUser u) throws SQLException {
        String sql = "UPDATE sys_user SET account=?,password=?,name=?,sex=?,deptid=?,email=?,phone=?,`status`=? WHERE id=?";
        Object[] objects = {u.getAccount(), u.getPassword(), u.getName(), u.getSex(), u.getDeptid(), u.getEmail(), u.getPhone(), u.getStatus(), u.getId()};
        int num = qr.update(sql, objects);
        return num;
    }

    @Override
    public int delUser(int id) throws SQLException {
        String sql = "DELETE FROM sys_user WHERE id=?";
        return qr.update(sql, id);
    }

    /**
     * 重置密码
     *
     * @param id
     * @return 重置后的密码
     * @throws SQLException
     */
    @Override
    public String resetPassword(int id) throws SQLException {
        String sql = "UPDATE sys_user SET password=? WHERE id=?";
        //生成6位数验证码
        String code = VerifyCodeUtils.getGenerateVerifyCode(6);
        Object[] obj = {code, id};
        qr.update(sql, obj);
        return code;
    }

    /**
     * 冻结
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int freeze(int id) throws SQLException {
        String sql = "UPDATE sys_user SET 'status'=? WHERE id=?";
        //生成6位数验证码
        Object[] obj = {3, id};

        return qr.update(sql, obj);
    }

    /**
     * 解除 冻结
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int unfreeze(int id) throws SQLException {
        String sql = "UPDATE sys_user SET 'status'=? WHERE id=?";
        //生成6位数验证码
        Object[] obj = {1, id};

        return qr.update(sql, obj);

    }

    /**
     * 角色分配
     *
     * @param id
     * @param roleid
     * @return
     * @throws SQLException
     */
    @Override
    public int roleAssignment(int id, int roleid) throws SQLException {
        String sql = "UPDATE sys_user SET 'roleid'=? WHERE id=?";
        //生成6位数验证码
        Object[] obj = {roleid, id};
        return qr.update(sql, obj);
    }

    @Override
    public List<UserManagEmentPaginationDisplay> ConditionalQuery(String str, String startTime, String endTime, Integer currentPageNo, Integer pageSize) throws SQLException {
        StringBuffer sb = new StringBuffer("SELECT u.id,u.`name`,u.sex,r.`name` as `部门`, d.simplename,u.email,u.phone,u.createtime,u.`status` " +
                "FROM sys_user as u " +
                "LEFT JOIN sys_role as r ON u.deptid = r.deptid " +
                "LEFT JOIN sys_dept as d ON r.num=d.num WHERE 1=1 ");
        //拼接分页
        StringBuffer sql = new StringBuffer(strJoint(sb, str, startTime, endTime));
        sql.append("limit ?,?");
        //定义数组用来补充占位符
        Object[] objects = new Object[]{(currentPageNo - 1) * pageSize, pageSize};
        //执行sql
        List<UserManagEmentPaginationDisplay> registerList = qr.query(sql.toString(), new BeanListHandler<UserManagEmentPaginationDisplay>(UserManagEmentPaginationDisplay.class), objects);
        return registerList;
    }

    /**
     * 对字符串进行拼接
     *
     * @param sb        原sql语句
     * @param rid       需要往sql里拼接的字符串rid
     * @param startTime 需要往sql里拼接的字符串开始时间
     * @param endTime   需要往sql里拼接的字符串结束时间
     * @return 拼接好的字符串
     */
    public String strJoint(StringBuffer sb, String rid, String startTime, String endTime) {
        //定义一个集合来拼接参数
//        List<Object> parmList  = new ArrayList<>();
        if (rid != null && !"".equals(rid)) {
            sb.append(" and  u.name  like " + "'%" + rid + "%'");
//            parmList.add( "%"+dname+"%");
        }
        if (startTime != null && !"".equals(startTime)) {
            sb.append(" and  u.createtime  > " + "'" + startTime + "'");
//            parmList.add(startTime);
        }
        if (endTime != null && !"".equals(endTime)) {
            sb.append(" and  u.createtime  < " + "'" + endTime + "'");
//            parmList.add(endTime);
        }
        System.out.println("拼接后的sql是:" + sb.toString());
        return sb.toString();
    }
}
